﻿Option Explicit On

Imports System.Text


Public Enum SecurityLevel
    NormalUser = 0
    BranchManager = 50
    AdminUser = 100
End Enum

Public Class Security
    Public Shared _SecurityLevel As SecurityLevel


    Public Shared Sub GetUserLevel()
        Dim DB As New CRM.DataLayer.DataAccess
        Try
            '_SecurityLevel = CType(DB.GetSQLScalar("select con_SecurityLevel from l_consultant where con_ntusername = '" & GlobalCRM.CurrentNTUserName & "'"), SecurityLevel)
            _SecurityLevel = CType(DB.GetSQLScalar("select con_SecurityLevel from l_consultant where con_name = '" & GlobalCRM.CurrentConName & "'"), SecurityLevel)
        Catch ex As Exception
            _SecurityLevel = SecurityLevel.NormalUser
        Finally
            DB = Nothing
        End Try

    End Sub

    Public Shared Function GroupMembership(ByVal ConsultantId As Integer) As DataTable
        Dim DB As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("SELECT Sec_UserMembers.SecUserMember_ID, Sec_UserGroups.SecGroup_ID, Sec_UserGroups.SecGroup_Name")
                .Append(" FROM Sec_UserGroups INNER JOIN Sec_UserMembers ON Sec_UserGroups.SecGroup_ID = Sec_UserMembers.SecUserMember_UserGroupID")
                .Append(" WHERE (Sec_UserMembers.SecUserMember_ConsultantID = " & ConsultantId & ")")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function GetBranchAllocPermission() As Integer
        Dim DB As New CRM.DataLayer.DataAccess
        Try

            Return DB.GetSQLScalar("select con_BranchAllocAccess from l_consultant where con_ntusername = '" & GlobalCRM.CurrentNTUserName & "'")

        Catch ex As Exception
            Return 0
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function PersonalPermissions(ByVal ConsultantId As Integer) As DataTable

        Dim DB As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("SELECT Sec_Links.SecLink_ID, Sec_Access.SecAccess_Name, SecLink_DtExp")
                .Append(" FROM Sec_Links INNER JOIN")
                .Append(" Sec_Access ON Sec_Links.SecLink_SecAccessID = Sec_Access.SecAccess_ID")
                .Append(" WHERE (Sec_Links.SecLink_ConsultantID = " & ConsultantId & ")")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function SecurityGroupList() As DataTable

        Dim DB As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("SELECT SecGroup_ID,SecGroup_Name as 'Group Name' FROM Sec_UserGroups")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function AddUserToGroup(ByVal ConsultantId As Integer, ByVal GroupId As Integer, ByVal ConName As String, ByVal UserName As String, ByVal GroupName As String) As Boolean

        Dim DB As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Dim cmn As New CommonFuncs
        Dim tsk As New TasksEvents
        Try
            SQL = New StringBuilder


            With SQL
                .Append("Insert Into Sec_UserMembers")
                .Append(" (SecUserMember_ConsultantID, SecUserMember_UserGroupID, SecUserMember_EnteredBy)")
                .Append(" VALUES (" & ConsultantId & ", " & GroupId & ", '" & cmn.SwapQuotes(ConName) & "')")
            End With

            If DB.ExecuteSQL(SQL.ToString) Then
                tsk.GblFunc_Add_SysEvent("Security", UserName & " added to security group " & GroupName)
                Return True
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
            cmn = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function DeleteUserFromGroup(ByVal MemberId As Integer, ByVal UserName As String, ByVal GroupName As String) As Boolean
        Dim DB As New CRM.DataLayer.DataAccess
        Dim task As New TasksEvents
        Try


            If DB.ExecuteSQL("Delete from Sec_UserMembers where SecUserMember_ID = " & MemberId) Then
                task.GblFunc_Add_SysEvent("Security", UserName & " removed from security group " & GroupName)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
            task = Nothing
        End Try
    End Function

    Public Shared Function SecurityAccessList() As DataTable
        Dim DB As New CRM.DataLayer.DataAccess
        Try
            Return DB.GetSQLDataTable("SELECT SecAccess_ID, SecAccess_Name FROM Sec_Access")
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
        End Try
    End Function

    Public Shared Function AddSecurityLink(ByVal AccessId As Integer, ByVal PermissionName As String, ByVal ConsultantId As Integer, ByVal ConsultantName As String, ByVal EnteredBy As String, ByVal EventMessage As String, Optional ByVal ExpiryDate As String = "") As Boolean

        Dim DB As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Dim task As New TasksEvents
        Dim cmn As New CommonFuncs
        Try
            SQL = New StringBuilder

            With SQL
                .Append("Insert into Sec_Links")
                .Append(" (SecLink_SecAccessID, SecLink_ConsultantID, SecLink_DTExp, SecLink_EnteredBy)")
                .Append(" Values(" & AccessId & ", " & ConsultantId & ", " & IIf(IsDate(ExpiryDate), ExpiryDate, "null") & ", '" & cmn.SwapQuotes(EnteredBy) & "')")
            End With

            If DB.ExecuteSQL(SQL.ToString) Then
                task.GblFunc_Add_SysEvent("Security", ConsultantName & " given security permission " & PermissionName & EventMessage)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
            SQL = Nothing
            task = Nothing
            cmn = Nothing
        End Try
    End Function

    Public Shared Function DeleteSecurityLink(ByVal LinkId As Integer, ByVal PermissionName As String, ByVal UserName As String) As Boolean
        Dim DB As New CRM.DataLayer.DataAccess

        Dim task As New TasksEvents
        Try

            If DB.ExecuteSQL("Delete from Sec_Links where SecLink_ID = " & LinkId) Then
                task.GblFunc_Add_SysEvent("Security", "Personal security permission " & PermissionName & " removed from user " & UserName)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing

            task = Nothing
        End Try
    End Function

    Public Shared Function GetAllGroupMembers(ByVal GroupId As Integer) As DataTable
        Dim DB As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("SELECT SecUserMember_ID, L_Consultant.con_name")
                .Append(" FROM Sec_UserMembers INNER JOIN L_Consultant ON Sec_UserMembers.SecUserMember_ConsultantID = L_Consultant.Con_ID")
                .Append(" WHERE (Sec_UserMembers.SecUserMember_UserGroupID = " & GroupId & ")")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            DB = Nothing
        End Try
    End Function

    Public Shared Function GetAllGroupPermissions(ByVal GroupId As Integer) As DataTable

        Dim DB As New CRM.DataLayer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("SELECT Sec_Links.SecLink_ID, Sec_Access.SecAccess_Name")
                .Append(" FROM Sec_Links INNER JOIN Sec_Access ON Sec_Links.SecLink_SecAccessID = Sec_Access.SecAccess_ID")
                .Append(" WHERE (Sec_Links.SecLink_SecUserGroupID = " & GroupId & ")")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            DB = Nothing
        End Try
    End Function

    Public Shared Function AddUserGroup(ByVal GroupName As String) As Boolean
        Dim DB As New CRM.DataLayer.DataAccess
        Dim cmn As New CommonFuncs
        Dim tsk As New TasksEvents
        Try
            If DB.ExecuteSQL("Insert into Sec_UserGroups (SecGroup_Name) VALUES('" & cmn.SwapQuotes(GroupName) & "')") Then
                tsk.GblFunc_Add_SysEvent("Security", "New user security group created, " & GroupName)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            cmn = Nothing
            tsk = Nothing
            DB = Nothing
        End Try
    End Function

    Public Shared Function CheckSecurityAccess(ByVal AccessItem As Integer) As Integer

        'Return 0 = no Access
        'Return 1 = Authorised
        Dim DB As New CRM.DataLayer.DataAccess

        Try
            CheckSecurityAccess = 0

            Dim sbSQL As New StringBuilder
            Dim dtbAccess As DataTable

            'check for group level access
            sbSQL.Append("SELECT Sec_Links.SecLink_ID, SecLink_DTExp")
            sbSQL.Append(" FROM Sec_Links INNER JOIN Sec_UserMembers ON Sec_Links.SecLink_SecUserGroupID = Sec_UserMembers.SecUserMember_UserGroupID")
            sbSQL.Append(" WHERE (Sec_Links.SecLink_SecAccessID = " & AccessItem & ") ")
            sbSQL.Append(" AND (Sec_UserMembers.SecUserMember_ConsultantID = " & GlobalCRM.CurrentConID & ")")

            dtbAccess = DB.GetSQLDataTable(sbSQL.ToString)

            If dtbAccess.Rows.Count > 0 Then
                If CDate(dtbAccess.Rows(0).Item("SecLink_DTExp").ToString) > Now() Or IsDBNull(dtbAccess.Rows(0).Item("SecLink_DTExp").ToString) Then CheckSecurityAccess = 1
            End If

            ' if no group access, check for personal access
            If CheckSecurityAccess = 0 Then

                sbSQL.Length = 0
                dtbAccess.Clear()

                sbSQL.Append("Select SecLink_ID, SecLink_DTExp from Sec_Links Where SecLink_ConsultantID = " & GlobalCRM.CurrentConID & " AND Sec_Links.SecLink_SecAccessID = " & AccessItem)
                dtbAccess = DB.GetSQLDataTable(sbSQL.ToString)

                If dtbAccess.Rows.Count > 0 Then
                    If CDate(dtbAccess.Rows(0).Item("SecLink_DTExp").ToString) > Now() Or IsDBNull(dtbAccess.Rows(0).Item("SecLink_DTExp").ToString) Then CheckSecurityAccess = 1
                End If

            End If
        Catch ex As Exception
            Return 0
        Finally


            DB = Nothing
        End Try
    End Function

End Class
